﻿--TẠO CSDL TRUNGTAM
CREATE DATABASE TrungTam;
--Sử dụng CSDL TrungTam
go
USE TrungTam;
--Tạo các bảng
--Bảng NHANVIEN
CREATE TABLE NhanVien(
MaNV varchar(10) not null,
HoTenNV nvarchar(50),
NgaySinh datetime,
GioiTinh nvarchar(10),
CMND varchar(9),
DienThoai varchar(30),
DiaChi nvarchar(250),
Email varchar(50),
ChucVu nvarchar(20),
Luong real,
MaNQL varchar(10),
constraint NHANVIEN_PK primary key (MaNV),
constraint NHANVIEN_DM_GioiTinh check( GioiTinh in(N'Nam',N'Nữ')),
);

--bảng NGUOIDUNG
create table NguoiDung(
TaiKhoan varchar(10) not null,
MatKhau varchar(30),
constraint NGUOIDUNG_PK primary key (TaiKhoan),
);

--bảng HOCVIEN_DANGKY
CREATE table HocVien_DangKy(
STT bigint IDENTITY not null,
MaLopDK varchar(10) not null,
HoVaTen nvarchar(50),
NgaySinh datetime,
GioiTinh nvarchar(10),
DiaChi nvarchar(250),
DienThoai varchar(30),
CMND varchar(9),
Email varchar(50),
NgayDangKi datetime,
constraint HOCVIEN_DANGKY_PK primary key(STT),
constraint HocVien_DangKy_DM_GioiTinh check(GioiTinh in(N'Nam',N'Nữ')),
);

--bảng HOCVIEN
create table HocVien(
MaHV varchar(10) not null,
HoTenHV nvarchar(50),
NgaySinh datetime,
GioiTinh nvarchar(10),
DiaChi nvarchar(250),
DienThoai varchar(30),
CMND varchar(9),
Email varchar(50),
constraint HOCVIEN_PK primary key(MaHV),
constraint HocVien_DM_GioiTinh check(GioiTinh in(N'Nam', N'Nữ')),
);

--bảng PHONGHOC
create table PhongHoc(
MaPhong varchar(10) not null,
TenPhong nvarchar(30),
LoaiPhong nvarchar(20),
MoTa nvarchar(250),
constraint PHONGHOC_PK primary key(MaPhong),	
);

--bảng LOPHOC
create table LopHoc(
MaLop varchar(10) not null,
MoTa nvarchar(250),
GiaoVienPT varchar(10),
PhongHoc varchar(10),
LichHoc nvarchar(100),
HocPhi integer,
NgayKhaiGiang datetime,
SiSo int,
constraint LOHOC_DM_SiSo check(SiSo >= 0),
constraint LOPHOC_PK primary key(MaLop),
);

--bảng HOCVIEN_LOPHOC
create table HocVien_LopHoc(
MaHV varchar(10) not null,
MaLop varchar(10) not null,
Diem1 integer,
Diem2 integer,
DongHocPhi bit,
constraint HOCVIEN_LOPHOC_PK primary key(MaHV, MaLop),
);
--Bảng ChatRoom
create table ChatRoom(
Id integer identity not null,
MaNV varchar(10),
TinNhan nvarchar(max),
NgayGio datetime,
constraint ChatRoom_PK primary key(Id),
);

--Bảng NguoiDungDangNhap
create table NguoiDungDangNhap(
MaNV varchar(10) not null,
NgayDangNhap datetime,
constraint NguoiDungDangNhap_PK primary key(MaNV),
);

--Bảng TaiLieuHocTap
create table TaiLieuHocTap(
MaTaiLieu int identity not null,
TenTaiLieu nvarchar(100),
Nguon varchar(100),
constraint TaiLieuHocTap_PK primary key(MaTaiLieu),
);
--Tạo khóa ngoại giữa các bảng
alter table NguoiDung add constraint NGUOIDUNG_FK_NHANVIEN foreign key (TaiKhoan) references NhanVien(MaNV) on delete cascade on update cascade;
alter table NhanVien add constraint NHANVIEN_FK_NHANVIEN foreign key (MaNQL) references NhanVien(MaNV);
alter table LopHoc add constraint LOPHOC_FK_NHANVIEN foreign key(GiaoVienPT) references NhanVien(MaNV) on delete set null on update cascade;
alter table LopHoc add constraint LOPHOC_FK_PHONGHOC foreign key(PhongHoc) references PhongHoc(MaPhong) on delete set null on update cascade;
alter table HocVien_LopHoc add constraint HOCVIEN_LOPHOC_FK_HOCVIEN foreign key(MaHV) references HocVien(MaHV) on delete cascade on update cascade;
alter table HocVien_LopHoc add constraint HOCVIEN_LOPHOC_FK_LOPHOC foreign key(MaLop) references LopHoc(MaLop) on delete cascade on update cascade;

--alter table NGUOIDUNG drop constraint NGUOIDUNG_FK_NHANVIEN;
--alter table NHANVIEN drop constraint NHANVIEN_FK_NHANVIEN;
--alter table LOPHOC drop constraint LOPHOC_FK_NHANVIEN;
--alter table LOPHOC drop constraint LOPHOC_FK_PHONGBAN;
--alter table HOCVIEN_LOPHOC drop constraint HOCVIEN_LOPHOC_FK_HOCVIEN;
--alter table HOCVIEN_LOPHOC drop constraint HOCVIEN_LOPHOC_FK_LOPHOC;

--Insert dữ liệu vào các bảng
--Inser dữ liệu cho bảng NHANVIEN
--Ngày sinh : tháng//ngày//năm
insert into NhanVien(MaNV,HoTenNV,NgaySinh,GioiTinh,CMND,DiaChi,DienThoai,Email,ChucVu,Luong) values('vudt',N'Đinh Tuấn Vũ','02/02/1991',N'Nam','222111333',N'Tam Hiệp Biên Hòa Đồng Nai','01656046307','tuanvu09spkt@gmail.com',N'Giám Đốc','0');
insert into NhanVien(MaNV,HoTenNV,NgaySinh,GioiTinh,CMND,DiaChi,DienThoai,Email,ChucVu,Luong) values('hungpt',N'Phan Tuấn Hùng','01/01/1991',N'Nam','333444555',N'Tân Bình tpHồ Chí Minh','0986632411','hungpt0306@gmail.com',N'Giám Đốc','0');
insert into NhanVien(MaNV,HoTenNV,NgaySinh,GioiTinh,CMND,DiaChi,DienThoai,Email,ChucVu,Luong) values('anhnt',N'Nguyễn Tuấn Anh','03/04/1991',N'Nam','678987665',N'Tam Hiệp Biên Hòa Đồng Nai','0165234567','justin34aries@gmail.com',N'Kế Toán','7000');
insert into NhanVien(MaNV,HoTenNV,NgaySinh,GioiTinh,CMND,DiaChi,DienThoai,Email,ChucVu,Luong) values('dungnx',N'Nguyễn Xuân Dũng','12/12/1991',N'Nam','111222333',N'Vĩnh Long','01654443322','dungnx@gmail.com',N'Kế Toán','7000');
insert into NhanVien(MaNV,HoTenNV,NgaySinh,GioiTinh,CMND,DiaChi,DienThoai,Email,ChucVu,Luong) values('nghianv',N'Nguyễn Văn Nghĩa','07/08/1991',N'Nam','567432123',N'Tam Hiệp Biên Hòa Đồng Nai','01658889977','nghiavnit@gmail.com',N'Bảo Vệ','5000');
insert into NhanVien(MaNV,HoTenNV,NgaySinh,GioiTinh,CMND,DiaChi,DienThoai,Email,ChucVu,Luong) values('thaontt',N'Nguyễn Thị Thanh Thảo','12/04/1991',N'Nữ','54367866',N'Vĩnh Cửu Đồng Nai','01657632211','thaont@gmail.com',N'Lao Công','5000');
insert into NhanVien(MaNV,HoTenNV,NgaySinh,GioiTinh,CMND,DiaChi,DienThoai,Email,ChucVu,Luong) values('tunm',N'Nguyễn Minh Tú','05/14/1991',N'Nam','5464776',N'Đồng Nai','0165753411','tunc@gmail.com',N'Giảng Viên','7000');
insert into NhanVien(MaNV,HoTenNV,NgaySinh,GioiTinh,CMND,DiaChi,DienThoai,Email,ChucVu,Luong) values('hanhnt',N'Nguyễn Thị Hạnh','12/24/1991',N'Nữ','12356788',N'Đồng Nai','0165344211','hanhnt@gmail.com',N'Giảng Viên','7000');

update NhanVien set MaNQL='vudt' where MaNV='anhnt';
update NhanVien set MaNQL='vudt' where MaNV='thaontt';
update NhanVien set MaNQL='hungpt' where MaNV='dungnx';
update NhanVien set MaNQL='hungpt' where MaNV='nghianv';
update NhanVien set MaNQL='hungpt' where MaNV='tunm';
update NhanVien set MaNQL='vudt' where MaNV='hanhnt';
--Insert dữ liệu cho bảng NGUOIDUNG
insert into NguoiDung(TaiKhoan,MatKhau) values('vudt','123456');
insert into NguoiDung(TaiKhoan,MatKhau) values('hungpt','123456');
insert into NguoiDung(TaiKhoan,MatKhau) values('anhnt','123456');
insert into NguoiDung(TaiKhoan,MatKhau) values('dungnx','iloveyou');
--INSERT dữ liệu cho bảng PHONGHOC
--Xem như tất cả các phòng đều có số lượng chứa tối đa là 100 người
insert into PhongHoc(MaPhong,TenPhong,LoaiPhong,MoTa) values('A101',N'A101',N'Phòng Học',N'Phòng dành cho việc giảng dạy');
insert into PhongHoc(MaPhong,TenPhong,LoaiPhong,MoTa) values('A102',N'A102',N'Phòng Học',N'Phòng dành cho việc giảng dạy');
insert into PhongHoc(MaPhong,TenPhong,LoaiPhong,MoTa) values('A103',N'A103',N'Phòng Học',N'Phòng dành cho việc giảng dạy');
insert into PhongHoc(MaPhong,TenPhong,LoaiPhong,MoTa) values('A104',N'A104',N'Phòng Kế Toán',N'Phòng dành cho nhân viên kế toán');
insert into PhongHoc(MaPhong,TenPhong,LoaiPhong,MoTa) values('A105',N'A105',N'Phòng Giám Đốc',N'Phòng dành cho giám đốc');

--insert dữ liệu cho bảng HOCVIEN
insert into HocVien(MaHV,HoTenHV,NgaySinh,GioiTinh,DiaChi,DienThoai,CMND,Email) values('HV1',N'Nguyễn Tuấn Tú','12/12/1993',N'Nam',N'tpHồ Chí Minh','0123546456','656756344','okmen@yahoo.com');
insert into HocVien(MaHV,HoTenHV,NgaySinh,GioiTinh,DiaChi,DienThoai,CMND,Email) values('HV2',N'Trịnh Thăng Bình','07/07/1995',N'Nam',N'tpHồ Chí Minh','024534536','543453234','hehehe@yahoo.com');
insert into HocVien(MaHV,HoTenHV,NgaySinh,GioiTinh,DiaChi,DienThoai,CMND,Email) values('HV3',N'Đào Trúc Anh','08/09/1992',N'Nữ',N'Sài Gòn','034536456','543453234','girlxinh@yahoo.com');
--insert dữ liệu cho bảng LOPHOC
insert into LopHoc(MaLop,MoTa,GiaoVienPT,PhongHoc,LichHoc,NgayKhaiGiang,HocPhi, SiSo) values('TOEIC100',N'Toiec starter, mức độ dễ nhất cho người mới bắt đầu','hanhnt','A101',N'2-3-4 18:00-19:00pm','05/01/2013','2000','100');
insert into LopHoc(MaLop,MoTa,GiaoVienPT,PhongHoc,LichHoc,NgayKhaiGiang,HocPhi, SiSo) values('TOEIC350',N'Toeic 350, mức độ trung bình','hanhnt','A101',N'5-6-7 18:00-19:00pm','02/01/2013','3000','100');
insert into LopHoc(MaLop,MoTa,GiaoVienPT,PhongHoc,LichHoc,NgayKhaiGiang,HocPhi, SiSo) values('TINA100',N'Tin học loại A, mức độ dễ nhất cho người mới bắt đầu','tunm','A102',N'2-3-4 8:00-9:00am','05/05/2012','1500','100');
insert into LopHoc(MaLop,MoTa,GiaoVienPT,PhongHoc,LichHoc,NgayKhaiGiang,HocPhi, SiSo) values('TINB100',N'Tin học loại B, mức độ trung bình','tunm','A103',N'7-CN 18:00-21:00pm','02/01/2013','1000','100');
--INSERT DỮ LIỆU CHO BẢNG HOCVIEN_LOPHOC
insert into HocVien_LopHoc(MaHV,MaLop,Diem1,Diem2,DongHocPhi) values('HV1','TINA100','10','10','True');